﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TempCtr.Model
{
    internal class MySqlBase
    {
        public static string ConStr { get; } = ConfingModel.MySqlCon;

        /// <summary>
        /// 获取连接这里只用了一个连接没有连接池处理
        /// </summary>
        public static List<MySqlConnection> ConnList = new List<MySqlConnection>();
        public static MySqlConnection GetConnection()
        {
            foreach(var conn in ConnList)
            {
                try
                {
                    conn.Close();
                    if (conn.State != ConnectionState.Open) conn.Open();
                    return conn;
                }
                catch
                {
                    continue;
                }
            }
            var con = new MySqlConnection(ConStr);
            con.Open();
            ConnList.Add(con);
            return con;
        }

        #region  MySql操作
        /// <summary>
        /// 得到数据
        /// </summary>
        /// <param name="sql">sql查询语句</param>
        /// <returns></returns>
        public static DataTable GetDataTabel(string sql, params MySqlParameter[] Parameters)
        {
            MySqlConnection conn = GetConnection();

            using (MySqlCommand cmd = new MySqlCommand(sql, conn))
            {
                try
                {
                    // 依次把参数传入命令文本
                    if (Parameters != null)
                    {
                        cmd.Parameters.AddRange(Parameters);
                    }

                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine(ex);
                    return null;
                }
            }
        }

        /// <summary>
        /// 得到多个数据表
        /// </summary>
        /// <param name="sql">sql查询语句</param>
        /// <param name="Parameters">参数避免字符串拼接</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, params MySqlParameter[] Parameters)
        {
            MySqlConnection conn = GetConnection();

            using (MySqlCommand cmd = new MySqlCommand(sql, conn))
            {
                try
                {
                    // 依次把参数传入命令文本
                    if (Parameters != null)
                    {
                        cmd.Parameters.AddRange(Parameters);
                    }

                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    DataSet dt = new DataSet();
                    da.Fill(dt);
                    return dt;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine(ex);
                    return null;
                }
            }
        }


        /// <summary>
        /// 对数据进行 增 删 改 的操作
        /// </summary>
        /// <param name="Pname">代参SQL语句</param>
        /// <param name="Parameters">SQL中的参数</param>
        /// <returns>result:sql对数据表影响的行数</returns>
        public static int ExecuteSQL(string sql, params MySqlParameter[] Parameters)
        {
            MySqlConnection conn = GetConnection();

            using (MySqlCommand cmd = new MySqlCommand(sql, conn))
            {
                try
                {
                    if (Parameters != null)
                    {
                        cmd.Parameters.AddRange(Parameters);
                    }
                    return cmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine(ex);
                    return 0;
                }
            }
        }

        /// <summary>
        /// 对数据进行 增 删 改 的操作返回ID
        /// </summary>
        /// <param name="Pname">代参SQL语句</param>
        /// <param name="Parameters">SQL中的参数</param>
        /// <returns>主键</returns>
        public static long ExecuteSQLReturnID(string sql, params MySqlParameter[] Parameters)
        {
            MySqlConnection conn = GetConnection();

            using (MySqlCommand cmd = new MySqlCommand(sql, conn))
            {
                try
                {
                    if (Parameters != null)
                    {
                        cmd.Parameters.AddRange(Parameters);
                    }
                    cmd.ExecuteNonQuery();
                    return cmd.LastInsertedId;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine(ex);
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        /// <param name="Parameters">参数</param>
        /// <returns></returns>
        public static int ExecuteProc(string sql, params MySqlParameter[] Parameters)
        {
            MySqlConnection conn = GetConnection();
            using (MySqlCommand cmd = new MySqlCommand(sql, conn))
            {
                try
                {
                    // 依次把参数传入命令文本
                    if (Parameters != null)
                    {
                        cmd.Parameters.AddRange(Parameters);
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine(ex);
                    return 0;
                }
            }
        }
        #endregion
    }
}
